package xyh.ContributionSystem.ProjectMember;

import xyh.ContributionSystem.ContributionSearch.SearchMember;
//import xyh.DAO.DAOBase;
import Connection.DAOBase;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


public class ProjectMemberDAOImpl extends DAOBase implements ProjectMemberDAO {
    private static final String MEMBER_INSERT_SQL = "INSERT INTO pro_mem (student_id, p_id, pm_status) VALUES (?,?,?)";
    private static final String MEMBER_UPDATE_SQL = "UPDATE pro_mem SET pm_status = ? WHERE student_id = ? AND p_id = ?";
    private static final String MEMBER_SELECT_SQL = "SELECT * FROM pro_mem WHERE student_id = ? AND p_id = ?";
    private static final String MEMBER_SELECT_ALL_SQL = "SELECT * FROM pro_mem";

    @Override
    public boolean addProjectMember(ProjectMember projectMember) {
        Connection con = null;
        try {
            con = getConnection();
            PreparedStatement psmt = con.prepareStatement(MEMBER_INSERT_SQL);
            psmt.setInt(1, projectMember.getStudent_id());
            psmt.setInt(2, projectMember.getP_id());
            psmt.setInt(3, projectMember.getPm_status());
            psmt.executeUpdate();
            psmt.close();
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return true;
    }

    @Override
    public boolean updateProjectMember(ProjectMember projectMember) {
        Connection con = null;
        try {
            con = getConnection();
            // 创建预编译语句对象
            PreparedStatement psmt = con.prepareStatement(MEMBER_UPDATE_SQL);
            // 设置参数值
            psmt.setInt(2, projectMember.getStudent_id());
            psmt.setInt(3, projectMember.getP_id());
            psmt.setInt(1, projectMember.getPm_status());
            // 执行修改操作
            psmt.executeUpdate();
            psmt.close();
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return true;
    }

    @Override
    public ProjectMember getProjectMember(ProjectMember projectMember) {
        Connection con = null;
        try {
            con = getConnection();
            PreparedStatement psmt = con.prepareStatement(MEMBER_SELECT_SQL);
            psmt.setInt(1, projectMember.getStudent_id());
            psmt.setInt(2, projectMember.getP_id());
            ResultSet rs = psmt.executeQuery();
            if (rs.next()) {
                int student_id = rs.getInt("student_id");
                int p_id = rs.getInt("p_id");
                int pm_status = rs.getInt("pm_status");
                ProjectMember result = ProjectMember.builder().student_id(student_id).p_id(p_id).pm_status(pm_status).build();
                rs.close();
                psmt.close();
                return result;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    @Override
    public List<ProjectMember> getAllProjectMembers() {
        List<ProjectMember> projectMembers = new ArrayList<>();
        Connection con = null;
        try {
            con = getConnection();
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(MEMBER_SELECT_ALL_SQL);
            while (rs.next()) {
                int student_id = rs.getInt("student_id");
                int p_id = rs.getInt("p_id");
                int pm_status = rs.getInt("pm_status");
                ProjectMember projectMember = new ProjectMember(student_id, p_id, pm_status);
                projectMembers.add(projectMember);
            }
            rs.close();
            stmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return projectMembers;
    }

    @Override
    public List<ProjectMember> findProjectMember(SearchMember searchMember) {
        List<ProjectMember> projectMembers = new ArrayList<>();
        Connection con = null;
        try {
            // 构建SQL语句
            StringBuilder sqlBuilder = new StringBuilder("SELECT * FROM pro_mem WHERE 1=1");
            if (searchMember.getStudent_id() != 0) {
                sqlBuilder.append(" AND student_id = ?");
            }
            if (searchMember.getP_id() != 0) {
                sqlBuilder.append(" AND p_id = ?");
            }
            if (searchMember.getPm_status() != 0) {
                sqlBuilder.append(" AND pm_status = ?");
            }
            String sql = sqlBuilder.toString();
            // 执行查询
            con = getConnection();
            PreparedStatement psmt = con.prepareStatement(sql);
            int index = 1;
            if (searchMember.getStudent_id() != 0) {
                psmt.setInt(index++, searchMember.getStudent_id());
            }
            if (searchMember.getP_id() != 0) {
                psmt.setInt(index++, searchMember.getP_id());
            }
            if (searchMember.getPm_status() != 0) {
                psmt.setInt(index++, searchMember.getPm_status());
            }
            ResultSet rs = psmt.executeQuery();
            while (rs.next()) {
                int student_id = rs.getInt("student_id");
                int p_id = rs.getInt("p_id");
                int pm_status = rs.getInt("pm_status");
                ProjectMember projectMember = new ProjectMember(student_id, p_id, pm_status);
                projectMembers.add(projectMember);
            }
            rs.close();
            psmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return projectMembers;
    }

}
